<!doctype html public "-//w3c//dtd html 4.0 transitional//en">


<!-- WARNING! This file is generated. -->
<!-- To alter documentation, edit files in src directory -->


<html><head>
<title>utPLSQL Package</title>
<link rel="stylesheet" href="utplsql.css" content="text/css">
<meta name="keywords" content="utPLSQL, PL\SQL, Unit Testing, Framework, Oracle"/>
<meta name="description" content="Unit Testing PL\SQL"/>
<meta name="title" content="utPLSQL Package"/>
<meta name="author" content="Steven Feuerstein, Chris Rimmer, Patrick Barel"/>
<meta name="copyright" content="(C) 2000-2005 Steven Feuerstein, Chris Rimmer, Patrick Barel"/>
</head><body>
<div class="purple_bar"><a href="index.html"><img src="utplsql.jpg" border=0></a></div>
<p>[ <A href="index.html">Home</A>
 | <A href="started.html">Getting Started</A>
 | <A href="buildpack.html">Build Test Packages</A>
 | <A href="examples.html">Examples</A>
 | <A href="userguide.html">User Guide</A>
 | <A href="release.html">Release Notes</A>
 | <A href="map.html">Document Map</A> ]</p>
<p><A href="userguide.html">&lt; Previous Section: User Guide</A> | <A href="utconfig.html">Next Section: utConfig Package &gt;</A></p>
<!-- Begin utPLSQL Body -->
<!-- $Id: utplsql.html,v 1.6 2005/05/10 14:23:20 chrisrimmer Exp $ -->
<h1>utPLSQL Package</h1>

<p>The utPLSQL package offers the following capabilities: <br>
  </p>

<table cellpadding="0" border="0" cellspacing="5">
 <tr>
  <td width="25%">
  <p><a href="#utplsql.test">utPLSQL.test</a><br>
  <a href="#utplsql.test">utPLSQL.run</a></p>
  </td>
  <td>
  <p><a href="#utplsql.test">Run a test</a></p>
  </td>
 </tr>
 <tr>
  <td>
  <p><a href="#utplsql.testsuite">utPLSQL.testsuite</a><br>
  <a href="#utplsql.testsuite">utPLSQL.runsuite</a></p>
  </td>
  <td>
  <p><a href="#utplsql.testsuite">Run a test suite</a></p>
  </td>
 </tr>

 <tr>
  <td>
  <p><a href="#Version">utPLSQL.version</a></p>
  </td>
  <td>
  <p><a href="#Version">Get the version of utPLSQL</a></p>
  </td>
 </tr>
 <tr>
  <td>
     <p><a href="#Trace">utPLSQL.trc</a><br>
     <a href="#Trace">utPLSQL.notrc</a><br>
     <a href="#Trace">utPLSQL.tracing</a></p>
  </td>
  <td>
     <p><a href="#Trace">Control utPLSQL's tracing mechanism</a></p>
  </td>
</tr>
  <tr>
  <td>
  <p><a href="#Register">utPLSQL.addtest</a></p>
  </td>
  <td>
  <p><a href="#Register">Register a unit test in a test package</a></p>
  </td>
 </tr>
</table>

<h2><a name="utplsql.test"></a>Run a Test or Test Suite</h2>

<p>With utPLSQL, you can run all the unit tests contained in a
single test package, or run the tests for a series of test packages defined in
a test <i>suite</i>. </p>

<p>The utPLSQL package offers two procedures, test and testsuite, to make it
easy for you to run &quot;red light, green light&quot; tests. Before you can
use these programs, however, you must <a href="buildpack.html">build
your own test package</a>. </p>

<p>To run a test for a single package, use the utPLSQL.test procedure: </p>

<pre>PROCEDURE utPLSQL.test (
   package_in IN VARCHAR2,
   samepackage_in IN BOOLEAN := FALSE,
   prefix_in IN VARCHAR2 := NULL,
   recompile_in IN BOOLEAN := TRUE,
   dir_in IN VARCHAR2 := NULL,
   suite_in in VARCHAR2 := NULL,
   owner_in IN VARCHAR2 := NULL,
   reset_results_in IN BOOLEAN := TRUE ,
   from_suite_in         IN   BOOLEAN := FALSE,
   subprogram_in         IN   VARCHAR2 := '%',
   per_method_setup_in   IN   BOOLEAN := FALSE
);</pre>

<p>where the parameters are defined as follows: <br>
  </p>

<table border="1" cellpadding="0" width="100%">
 <tr>
  <td width="19%">
  <p>package_in</p>
  </td>
  <td width="79%">
  <p>The name of the package or stand-alone program to be
  tested.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>samepackage_in</p>
  </td>
  <td width="79%">
  <p>Pass TRUE if your unit test programs are defined in the same
  package as the source code to be tested. The default is that you have created
  a separate package.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>prefix_in</p>
  </td>
  <td width="79%">
  <p>The prefix to be appended to package_in to come up with
  the name of the test package. If you do not provide a value, the last prefix
  you specified (or the default) will be used.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>recompile_in</p>
  </td>
  <td width="79%">
  <p>Pass FALSE if you do not want utPLSQL to <a href="utconfig.html#Autocompile">automatically recompile</a> your test package
  before running the test.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>dir_in</p>
  </td>
  <td width="79%">
  <p>The directory containing the test package source code. If
  you do not provide a value in your call to utPLSQL.test (the default) and if
  you have not turned off automatic recompilation, utPLSQL will look for the
  test package source code in the directory specified by a call to <a href="utconfig.html#Setdir">utConfig.setdir</a>. If you do not provide a
  value, the last directory you specified (if any) will be used.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>suite_in</p>
  </td>
  <td width="79%">
  <p>The name of the suite that contains the specified test
  package. This is an optional value and is used to update <a href="#Statistics">statistics</a>
  for the test.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>owner_in</p>
  </td>
  <td width="79%">
  <p>The name of the schema that was specified when the test
  suite was defined and the packaged added to the suite.  This is an
  optional value and is used to update <a href="#Statistics">statistics</a> for
  the test.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>reset_results_in</p>
  </td>
  <td width="79%">
  <p>Pass FALSE to tell utPLSQL to <i>not</i> reset the results
  information, in which case you will still be able to view results by calling <a href="utresult.html">utResult.show</a> . Otherwise, utPLSQL clears the result
  data after each test.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>from_suite_in         </p>
  </td>
  <td width="79%">
  <p>Pass TRUE to tell utPLSQL that this test is being run from
  within a test suite (for internal use only).</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>subprogram_in         </p>
  </td>
  <td width="79%">
  <p>Pass a string to restrict which of the test procedures
  will be executed for this run. Default of % means all tests will be run.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>per_method_setup_in   </p>
  </td>
  <td width="79%">
  <p>Pass TRUE to run the setup and teardown procedure before
  and after each unit test procedure is executed. Default of FALSE means that
  these programs will be run once, at the start and end of the package test
  execution as a whole.</p>
  </td>
 </tr>
<!-- added by Jens Schauder -->
 <tr>
  <td width="19%">
  <p>override_package_in </p>
  </td>
  <td width="79%">
  <p>Override the automatic determination of package names thus removing the
  one to one relationship between test package and package to test. 
  Default is NULL. Instead of using this parameter consider the procedure <a href="#utplsql.run">run</a>.</p>
  </td>
 </tr>
<!-- end of addition -->
</table>

<p>Here are some examples of using the utPLSQL.test procedure: </p>

<p>1. Run the unit test for the betwnstr function (by executing the ut_betwnstr
test package, since the default prefix is used). Do not recompile the test
package. </p>

<pre>SQL&gt; exec utPLSQL.test ('betwnstr', recompile_in =&gt; FALSE)</pre>

<p>2. Run all of the unit tests for the te_employee package,
stored in a test package called &quot;test_te_employee&quot; in the /tmp
directory. Recompile the test package before execution. </p>

<pre>SQL&gt; exec utPLSQL.test ('te_employee', prefix_in =&gt; 'test_', dir_in =&gt; '/tmp')</pre>

<p>3. Run all the unit tests for the corporate_polluters
package, located in the same package as the source code. </p>

<pre>SQL&gt; exec utPLSQL.test ('te_employee', samepackage_in =&gt; TRUE)</pre>

<p>Since utPLSQL follows the red light-green light approach on
reporting results, each time you run utPLSQL.test, it will display the results.
If successful, you will see output like this: </p>

<pre>SUCCESS: &quot;betwnstr&quot;</pre>

<p>If the test fails at some point, you will see output like
this: </p>

<pre>FAILURE: &quot;betwnstr&quot;
BETWNSTR: IS NULL: NULL start
BETWNSTR: End larger than string length; expected "cdeg", got "cdefg"</pre>

<!-- added by Jens Schauder -->
<h3><a name="utplsql.run"></a>Running a Test the other way</h3>

<p> The normal usage of the test procedure as described above assumes that for
each package you want to test, say <code>mypackage</code>, has a package for
testing this package having the same name but with an additional prefix:
<code>ut_mypackage</code>. Instead of using this approach,  you can use the procedure
<code>run</code>. This procedure runs a test package directly without any further 
conditions on the name or other packages. The only condition that still applies
is the naming conventions necessary to make it a valid test package.
</p>
<pre>
PROCEDURE run (
    testpackage_in      IN VARCHAR2,
    prefix_in           IN VARCHAR2 := NULL,
    suite_in            IN VARCHAR2 := NULL,
    owner_in            IN VARCHAR2 := NULL,
    reset_results_in    IN BOOLEAN  := TRUE,
    from_suite_in       IN BOOLEAN  := FALSE,
    subprogram_in       IN VARCHAR2 := '%',
    per_method_setup_id IN BOOLEAN  := FALSE);
</pre>

<p>where the parameters are defined as follows: <br>
  </p>

<table border="1" cellpadding="0" width="100%">
 <tr>
  <td width="19%">
  <p>test_package_in</p>
  </td>
  <td width="79%">
  <p>The name of the test package to run.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>prefix_in</p>
  </td>
  <td width="79%">
  <p>The prefix to be appended to package_in to come up with
  the name of the test package. If you do not provide a value, NULL is used as 
  a default. i.e. the package name is used as provided in the first parameter</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>suite_in</p>
  </td>
  <td width="79%">
  <p>The name of the suite that contains the specified test
  package. This is an optional value and is used to update <a href="#Statistics">statistics</a>
  for the test.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>owner_in</p>
  </td>
  <td width="79%">
  <p>The name of the schema that was specified when the test
  suite was defined and the packaged added to the suite.  This is an
  optional value and is used to update <a href="#Statistics">statistics</a> for
  the test.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>reset_results_in</p>
  </td>
  <td width="79%">
  <p>Pass FALSE to tell utPLSQL to <i>not</i> reset the results
  information, in which case you will still be able to view results by calling <a href="utresult.html">utResult.show</a> . Otherwise, utPLSQL clears the result
  data after each test.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>from_suite_in</p>
  </td>
  <td width="79%">
  <p>Pass TRUE to tell utPLSQL that this test is being run from
  within a test suite (for internal use only).</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>subprogram_in</p>
  </td>
  <td width="79%">
  <p>Pass a string to restrict which of the test procedures
  will be executed for this run. Default of <code>'%'</code> means all tests will be run.</p>
  </td>
 </tr>
 <tr>
  <td width="19%">
  <p>per_method_setup_in</p>
  </td>
  <td width="79%">
  <p>Pass TRUE to run the setup and teardown procedure before
  and after each unit test procedure is executed. Default of FALSE means that
  these programs will be run once, at the start and end of the package test
  execution as a whole.</p>
  </td>
 </tr>
</table>

<!-- end of addition -->

<h3><a name="utplsql.testsuite"></a>Running a Test Suite</h3>

<p>In addition to running a test for a single test package, you
can set up a test suite that consists of one or more test packages. You can
then run an entire suite of tests with a call to utPLSQL.testsuite: </p>

<pre>PROCEDURE utPLSQL.testsuite (
   suite_in IN VARCHAR2,
   recompile_in IN BOOLEAN := TRUE,
   reset_results_in IN BOOLEAN := TRUE
   per_method_setup_in in BOOLEAN := FALSE
   );</pre>

<p>where suite_in is the name of the suite and recompiled_in
determines the <a href="utconfig.html#Autocompile">auto compilation behavior</a>.
</p>

<p>Here is an example of the call I would make to run all my tests for the
PL/Vision library: </p>

<pre>SQL&gt; exec utplsql.testsuite ('plvision');</pre>

<p>The parameter list for utPLSQL.testSuite is much shorter
than utPLSQL.test; rather than pass information like directory, owner name and
same-package through a parameter list, you define these characteristics in the
suite itself (stored in a series of utPLSQL tables). </p>

<p>Before you can test an entire suite, you must <a href="defsuite.html">define
the suite</a>. </p>

<!-- added by Jens Schauder -->
<h3><a name="utplsql.runsuite"></a>Running a Test Suite the other way</h3>

<p>Similiar to the <a href="#utplsql.run">run</a> procedure for single packages, there is the
<code>runsuite</code> procedure to run testsuites. When you use this procedure 
there is no relationship assumed between the names of test packages specified in the
test suite and the procedures to be tested.
</p>

<pre>PROCEDURE utPLSQL.runsuite (
   suite_in IN VARCHAR2,
   reset_results_in IN BOOLEAN := TRUE
   per_method_setup_in in BOOLEAN := FALSE
   );</pre>
   
<p>
The usage of the parameters is just as in <a href="#utplsql.testsuite">testsuite</a>. 
</p>
<!-- end of addition -->

<h3><a name="Statistics"></a>Recording and Accessing Test Statistics</h3>

<p>If you have defined test suites, and packages within those
test suites, utPLSQL will update those definitions with the follow statistics
after each test is run: </p>

<ul type="disc">
 <li>Status of last run: success
     or failure?</li>
 <li>Start and end times of last
     run</li>
 <li>Total number of failures</li>
 <li>Total number of executions of
     the test</li>
</ul>

<p>All of this is done for you automatically. You can then write queries and reports against the
ut_package and ut_suite tables. </p>


<h2><a name="Version"></a>Return utPLSQL version</h2>

<p>Run the utPLSQL.version function to return the version of
utPLSQL you have installed: </p>

<pre>FUNCTION utPLSQL.version RETURN VARCHAR2</pre>

<h2><a name="Trace"></a>utPLSQL Trace</h2>

<p>These routines are very simple and take no arguments:</p>

<pre>
PROCEDURE trc;

PROCEDURE notrc;

FUNCTION tracing RETURN BOOLEAN;
</pre>

<p>The procedures trc and notrc are used to turn tracing on and off
respectively.  The function tracing returns TRUE if tracing is currently turned
on and FALSE otherwise.  This facility is useful when writing code in utPLSQL
(the framework itself, not your test code).  An example of the output generated
is:</p>

<pre>
Initialized utPLSQL session...
Setpkg to Lottery
Package and program = ut_Lottery
Same package? N
Is package? Y
Prefix = ut_
Recompiling ut_Lottery in
Runprog of ut_SETUP
Package and program = ut_Lottery.ut_SETUP
Same package? N
Is package? Y
Prefix = ut_
Addtest
Package and program = Lottery.UT_DRAW
Same package? N
Override? Y
Prefix = ut_
Runprog of UT_DRAW
Package and program = ut_Lottery.UT_DRAW
Same package? N
Is package? Y
Prefix = ut_
.
&gt;  FFFFFFF   AA     III  L      U     U RRRRR   EEEEEEE
&gt;  F        A  A     I   L      U     U R    R  E
&gt;  F       A    A    I   L      U     U R     R E
&gt;  F      A      A   I   L      U     U R     R E
&gt;  FFFF   A      A   I   L      U     U RRRRRR  EEEE
&gt;  F      AAAAAAAA   I   L      U     U R   R   E
&gt;  F      A      A   I   L      U     U R    R  E
&gt;  F      A      A   I   L       U   U  R     R E
&gt;  F      A      A  III  LLLLLLL  UUU   R     R EEEEEEE
.
FAILURE: "Lottery"
.
&gt; Individual Test Case Results:
&gt;
FAILURE - EQ "Test of DRAW" Expected "01 02 05 27 43 49" and got "02 04 27 28 31 33"
&gt;
&gt;
&gt; Errors recorded in utPLSQL Error Log:
&gt;
&gt; NONE FOUND
Runprog of ut_TEARDOWN
Package and program = ut_Lottery.ut_TEARDOWN
Same package? N
Is package? Y
Prefix = ut_

PL/SQL procedure successfully completed.
</pre>

<h2><a name="Register"></a>Register a Unit Test</h2>

<p><b>As of version 1.4.1, you
no longer have to explicitly register a unit test! </b>The default
behavior of utPLSQL is now to extract from the data dictionary (via the
ALL_ARGUMENTS data dictionary view) the names of all the unit test procedures
you have defined, and then run them. utPLSQL identifies these programs by
looking for all programs whose names start with the specified prefix.</p>

<p>If you decide that you want to explicitly register your unit
tests, then you will need to turn on manual registration:</p>

<pre>SQL&gt; exec utConfig.registertest (TRUE)</pre>

<p>This setting is immediately saved in the database for your
schema. To turn off manual registration:</p>

<pre>SQL&gt; exec utConfig.registertest (FALSE)</pre>

<p><b>So read no further unless you have turned on manual
registration!</b> You might do this, for example, if you have already built a
number of test packages in a version of utPLSQL prior to 1.4.1 and do not want
to make any changes to your test package code.</p>

<p>All aspects of manual registration of unit tests for a
program or package actually occur within the Unit Test Package itself, in the <a href="howto.html#Setup">setup procedure</a>. No persistent unit test information
is stored between runs of the unit test, unless you define that unit test
within a test suite.</p>

<p>Use the utPLSQL.addtest procedure to register a unit test.</p>

<pre>   PROCEDURE utPLSQL.addtest (
      NAME_IN IN VARCHAR2,
      utprefix_in IN VARCHAR2,
      iterations_in IN PLS_INTEGER := 1
   );
 
   PROCEDURE utPLSQL.addtest (
      package_in IN VARCHAR2,
      NAME_IN IN VARCHAR2,
      utprefix_in IN VARCHAR2,
      iterations_in IN PLS_INTEGER := 1
   );</pre>

<p>where</p>

<p><b>name_in</b> is the name of the program you are
testing. Note that this is the name of the unit test procedure itself,
including the unit test prefix..</p>

<p><b>utprefix_in</b> is the prefix to be applied to
name_in to construct the unit tst procedure. This is currently NOT IN USE; only
the package prefix specified in your call to utPLSQL.test and utPLSQL.testsuite
is used.</p>

<p><b>iterations_in</b> is the number of times you wish to
run the test (currently NOT IN USE).</p>

<p><b>package_in</b> is the name of the package containing
the unit test procedure. If you provide a package name when you call
utPLSQL.addtest, you will override the package name set when you called
utPLSQL.test -- but only for that one test. We recommend that you not change
the package name.</p>

<p>Here is a setup procedure that sets up a series of tests for
a query-only encapsulation of the employee table:</p>

<pre>CREATE OR REPLACE PACKAGE BODY ut_te_employee
IS
   PROCEDURE ut_setup
   IS
   BEGIN
      utplsql.addtest ('UT_EMP_DEPT_LOOKUPROWCOUNT');
      utplsql.addtest ('UT_EMP_JOB_LOOKUPROWCOUNT');
      utplsql.addtest ('UT_EMP_MGR_LOOKUPROWCOUNT');
      utplsql.addtest ('UT_HIRE_DATE$VAL');
      utplsql.addtest ('UT_I_EMPLOYEE_NAME$ROW');
      utplsql.addtest ('UT_I_EMPLOYEE_NAME$VAL');
      utplsql.addtest ('UT_ONEROW');
      utplsql.addtest ('UT_PKYROWCOUNT');
      utplsql.addtest ('UT_ROWCOUNT');
      utplsql.addtest ('UT_SALARY$VAL');
   END;</pre>

<p>Once you have placed your addtest programs into your test
package's setup procedure, you are ready to <a href="buildpack.html">build your own unit tests</a>.</p>


<!-- End utPLSQL Body -->
<p><A href="userguide.html">&lt; Previous Section: User Guide</A> | <A href="utconfig.html">Next Section: utConfig Package &gt;</A></p>
<div class="purple_bar"><a href="index.html"><img src="utplsql.jpg" border=0></a></div>
<p class="copyright">Copyright (C) 2000-2005 <A href="mailto:steven@stevenfeuerstein.com">Steven Feuerstein<A>, <A href="mailto:c@24.org.uk">Chris Rimmer<A>, <A href="mailto:pbarel@vda.nl">Patrick Barel<A> All rights reserved</p>
</body></html>